SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[usp_StatisticsCompanyUpdate]
	(
      @param_JOL_Statistics_StartTime VARCHAR(50),
      @param_JOL_Statistics_EndTime VARCHAR(50)
	)
AS
SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
DECLARE @EmployerID INT, @RequireNUM INT, @RequirePeopleNUM INT, @ApplyFieldNUM INT, @LectureNUM INT, @Recruitment INT, @AgreementNUM INT
DECLARE @Count int, @NUM int , @i int

WHILE @param_JOL_Statistics_StartTime <= @param_JOL_Statistics_EndTime
BEGIN
	SELECT DISTINCT JOL_Employer_No
		INTO #1
		FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_Require] ON JOL_Employer_Account = JOL_Require_Company
		WHERE YEAR(JOL_Require_AddTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Require_AddTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Require_VerifyStatus = '已审核'
	ALTER TABLE #1 ADD [ID] INT IDENTITY
	SET @NUM = (SELECT COUNT(*) FROM #1)
	SET @i = 1
	WHILE @i <= @NUM
	BEGIN
		SET @EmployerID = (SELECT JOL_Employer_No FROM #1 WHERE ID = @i)
		SET @i = @i + 1
		
		SET @RequireNUM = (SELECT count(*)  
			FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_Require] ON JOL_Employer_Account = JOL_Require_Company
			WHERE JOL_Employer_No = @EmployerID
				AND YEAR(JOL_Require_AddTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_Require_AddTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_Require_VerifyStatus = '已审核')
		SET @RequirePeopleNUM = (SELECT SUM(JOL_Require_Number) 
			FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_Require] ON JOL_Employer_Account = JOL_Require_Company
			WHERE JOL_Employer_No = @EmployerID
				AND YEAR(JOL_Require_AddTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_Require_AddTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_Require_VerifyStatus = '已审核')

		SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_EmployerActivity 
			WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime)
		IF (@Count = 0)
			BEGIN
				INSERT INTO [dbo].[JOL_Statistics_EmployerActivity]
				(
					Statistics_EmployerID,
					Statistics_Date,
					Statistics_RequireNUM,
					Statistics_RequirePeopleNUM,
					Statistics_ApplyFieldNUM,
					Statistics_LectureNUM,
					Statistics_Recruitment,
					Statistics_AgreementNUM,
					Statistics_AddTime,
					Statistics_UpdateTime
				)
				VALUES
				(
					@EmployerID,
					@param_JOL_Statistics_StartTime,
					@RequireNUM,
					@RequirePeopleNUM,
					0,
					0,
					0,
					0,
					GETDATE(),
					GETDATE()
				)
			END
		ELSE
			BEGIN
				UPDATE [dbo].[JOL_Statistics_EmployerActivity]
					SET Statistics_RequireNUM = @RequireNUM,
						Statistics_RequirePeopleNUM = @RequirePeopleNUM,
						Statistics_UpdateTime = GETDATE()
				WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime
			END
	END
	DROP TABLE #1
	
	SELECT DISTINCT JOL_Employer_No
		INTO #2
		FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Employer_Account = JOL_Field_ApplyCompany
		WHERE YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
			AND JOL_Field_VerifyStatus = '已审核'
	ALTER TABLE #2 ADD [ID] INT IDENTITY
	SET @NUM = (SELECT COUNT(*) FROM #2)
	SET @i = 1
	WHILE @i <= @NUM
	BEGIN
		SET @EmployerID = (SELECT JOL_Employer_No FROM #2 WHERE ID = @i)
		SET @i = @i + 1
		
		SET @ApplyFieldNUM = (SELECT COUNT(*)
			FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Employer_Account = JOL_Field_ApplyCompany
			WHERE JOL_Employer_No = @EmployerID
				AND YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_Field_VerifyStatus = '已审核')
		SET @LectureNUM = (SELECT COUNT(*)
			FROM[dbo].[JOL_Employer] INNER JOIN [dbo].[JOL_ApplyField] ON JOL_Employer_Account = JOL_Field_ApplyCompany
			WHERE JOL_Employer_No = @EmployerID
				AND YEAR(JOL_Field_ApplyTime) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(JOL_Field_ApplyTime) = MONTH(@param_JOL_Statistics_StartTime)
				AND JOL_Field_Purpose = '宣讲会'
				AND JOL_Field_VerifyStatus = '已审核')
		
		SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_EmployerActivity 
			WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime)
		IF (@Count = 0)
			BEGIN
				INSERT INTO [dbo].[JOL_Statistics_EmployerActivity]
				(
					Statistics_EmployerID,
					Statistics_Date,
					Statistics_RequireNUM,
					Statistics_RequirePeopleNUM,
					Statistics_ApplyFieldNUM,
					Statistics_LectureNUM,
					Statistics_Recruitment,
					Statistics_AgreementNUM,
					Statistics_AddTime,
					Statistics_UpdateTime
				)
				VALUES
				(
					@EmployerID,
					@param_JOL_Statistics_StartTime,
					0,
					0,
					@ApplyFieldNUM,
					@LectureNUM,
					0,
					0,
					GETDATE(),
					GETDATE()
				)
			END
		ELSE
			BEGIN
				UPDATE [dbo].[JOL_Statistics_EmployerActivity]
					SET Statistics_ApplyFieldNUM = @ApplyFieldNUM,
						Statistics_LectureNUM = @LectureNUM,
						Statistics_UpdateTime = GETDATE()
				WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime
			END
	END
	DROP TABLE #2
	
	SELECT DISTINCT b.employing_unit AS JOL_Employer_No
		INTO #3
		FROM[dbo].[JOL_Recruitment] AS a 
			INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
		WHERE YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
			AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
			AND a.[state] = 1
	ALTER TABLE #3 ADD [ID] INT IDENTITY
	SET @NUM = (SELECT COUNT(*) FROM #3)
	SET @i = 1
	WHILE @i <= @NUM
	BEGIN
		SET @EmployerID = (SELECT JOL_Employer_No FROM #3 WHERE ID = @i)
		SET @i = @i + 1
		
		SET @Recruitment = (SELECT COUNT(DISTINCT a.id)
			FROM[dbo].[JOL_Recruitment] AS a 
				INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
			WHERE b.employing_unit = @EmployerID
				AND YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
				AND a.[state] = 1)
		SET @AgreementNUM = (SELECT COUNT(*)
			FROM[dbo].[JOL_Recruitment] AS a 				
				INNER JOIN [dbo].[JOL_Recruitment_Participants_apply] AS b ON a.id = b.recruitment
				INNER JOIN [dbo].[JOL_Recruitment_Position] AS c ON b.id = c.participants_apply
				INNER JOIN [dbo].[JOL_Recruitment_ApplyingRecords] AS d ON c.id = d.recruit_position
			WHERE b.employing_unit = @EmployerID
				AND YEAR(a.start_time) = YEAR(@param_JOL_Statistics_StartTime) 
				AND MONTH(a.start_time) = MONTH(@param_JOL_Statistics_StartTime)
				AND a.[state] = 1
				AND d.result = '接收')
		SET @Count = (SELECT COUNT(*) FROM dbo.JOL_Statistics_EmployerActivity 
			WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime)
		IF (@Count = 0)
			BEGIN
				INSERT INTO [dbo].[JOL_Statistics_EmployerActivity]
				(
					Statistics_EmployerID,
					Statistics_Date,
					Statistics_RequireNUM,
					Statistics_RequirePeopleNUM,
					Statistics_ApplyFieldNUM,
					Statistics_LectureNUM,
					Statistics_Recruitment,
					Statistics_AgreementNUM,
					Statistics_AddTime,
					Statistics_UpdateTime
				)
				VALUES
				(
					@EmployerID,
					@param_JOL_Statistics_StartTime,
					0,
					0,
					0,
					0,
					@Recruitment,
					@AgreementNUM,
					GETDATE(),
					GETDATE()
				)
			END
		ELSE
			BEGIN
				UPDATE [dbo].[JOL_Statistics_EmployerActivity]
					SET Statistics_Recruitment = @Recruitment,
						Statistics_AgreementNUM = @AgreementNUM,
						Statistics_UpdateTime = GETDATE()
				WHERE Statistics_EmployerID = @EmployerID AND Statistics_Date = @param_JOL_Statistics_StartTime
			END
	END
	DROP TABLE #3
	SET @param_JOL_Statistics_StartTime = CONVERT(VARCHAR(50), DATEADD(MONTH, 1, @param_JOL_Statistics_StartTime), 23)
END

GO
